package c.main.jdbc;

import javax.sql.DataSource;
import java.sql.*;
import java.sql.Date;
import java.util.*;

/**
 * Author: chengwei
 * Date:   2016/3/23
 * Time:   16:57
 */
public class SqlJdbcTemplate implements JdbcTemplate {
    
    private DataSource ds;
    
    private Connection conn;

    public SqlJdbcTemplate(DataSource ds) {
        this.ds = ds;
    }

    @Override
    public DataSource getDataSource() {
        return ds;
    }

    @Override
    public void setAutoCommit(boolean b) {
        try {
            conn.setAutoCommit(b);
            log.debug("开启事务...");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void open() {
        try {
            conn = ds.getConnection();
            log.debug("获取连接...");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void commit() {
        try {
            if (!conn.getAutoCommit()) {
                conn.commit();
                log.debug("提交事务...");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    @Override
    public void close() {
        try {
            if (!conn.isClosed()) {
                conn.close();
                log.debug("关闭连接...");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void rollback() {
        try {
            conn.rollback();
            log.debug("回滚...");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public int executeInsert(String tableName, Map<String, Object> map) {
        return 0;
    }

    @Override
    public int executeUpdate(String tableName, Map<String, Object> where, Map<String, Object> set) {
        return 0;
    }

    @Override
    public int executeDelete(String tableName, Map<String, Object> where) {
        return 0;
    }

    @Override
    public int execute(String sql, Object... args) {
        log.debug(String.format("执行sql:%s,参数:%s",sql,getArgsInfo(args)));
        PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement(sql);
            setStatementArguments(stmt, args);
            return stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(String.format("执行sql[%s]时异常:%s",sql,e.getMessage()));
        } finally {
            free(stmt,null);
        }
    }

    @Override
    public Map<String, Object> queryForMap(String sql, Object... args) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Map<String,Object> map = new HashMap<>();
        try {
            stmt = conn.prepareStatement(sql);
            setStatementArguments(stmt, args);
            rs = stmt.executeQuery();
            ResultSetMetaData md;
            if (rs.next()) {
                md = rs.getMetaData();
                for (int i = 1;i <= md.getColumnCount();i++) {
                    map.put(md.getColumnLabel(i),getResultSetValue(rs,i));
                }
            }
            return map;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(String.format("执行sql[%s]时异常:%s",sql,e.getMessage()));
        } finally {
            free(stmt,rs);
        }
    }

    @Override
    public List<Map<String, Object>> queryForListMap(String sql, Object... args) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.prepareStatement(sql);
            setStatementArguments(stmt, args);
            rs = stmt.executeQuery();
            ResultSetMetaData md;
            Map<String,Object> map;
            List<Map<String,Object>> list = new ArrayList<>();
            while (rs.next()) {
                map = new HashMap<>();
                md = rs.getMetaData();
                for (int i = 1;i <= md.getColumnCount();i++) {
                    map.put(md.getColumnLabel(i),getResultSetValue(rs,i));
                }
                list.add(map);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(String.format("执行sql[%s]时异常:%s",sql,e.getMessage()));
        } finally {
            free(stmt,rs);
        }
    }

    @Override
    public int queryForInt(String sql, Object... args) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.prepareStatement(sql);
            setStatementArguments(stmt, args);
            rs =  stmt.executeQuery();
            if (rs.next())
            return rs.getInt(1);
            return 0;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(String.format("执行sql[%s]时异常:%s",sql,e.getMessage()));
        } finally {
            free(stmt,rs);
        }
    }


    /**
     * 为待执行的带参语句设置参数
     * @param stmt
     * @param args
     * @throws SQLException
     */
    private void setStatementArguments(PreparedStatement stmt,Object[] args) throws SQLException{
        if(null!=args && args.length>0){
            Object objVal;
            for(int i=1;i<=args.length;i++){
                objVal = args[i-1];
                if(objVal instanceof String){
                    stmt.setString(i,(String)objVal);
                }else if(objVal instanceof Date){
                    stmt.setTimestamp(i, new Timestamp(((Date) objVal).getTime()));
                }else if(objVal instanceof Integer){
                    stmt.setInt(i,(Integer)objVal);
                }else if(objVal instanceof Boolean){
                    stmt.setBoolean(i, (Boolean) objVal);
                }else if(objVal instanceof Float){
                    stmt.setFloat(i, (Float) objVal);
                }else if(objVal instanceof Double){
                    stmt.setDouble(i, (Double) objVal);
                }else if(objVal instanceof Long){
                    stmt.setLong(i, (Long) objVal);
                }else{
                    stmt.setObject(i,objVal);
                }
            }
        }
    }


    /**
     * 关闭数据库资源，在关闭前先判断是否为空
     * @param stmt
     * @param rs
     */
    private void free(Statement stmt,ResultSet rs){
        if(rs != null){
            try{
                rs.close();
            }catch(SQLException ignored){}
        }
        if(stmt != null){
            try{
                stmt.close();
            }catch(SQLException ignored){}
        }
    }

    public static Object getResultSetValue(ResultSet rs, int index) throws SQLException {
        Object obj = rs.getObject(index);
        if(obj == null){
            return null;
        }
        String className = obj.getClass().getName();
        if(obj instanceof String){
            return obj;
        }else if(obj instanceof Short){
            obj = Integer.valueOf(obj.toString());
        }else if (obj instanceof Blob) {
            obj = rs.getBytes(index);
        }else if (obj instanceof Clob) {
            obj = rs.getString(index);
        }else if (obj instanceof Timestamp) {
            Timestamp timestamp = rs.getTimestamp(index);
            obj = new Date(timestamp.getTime());
        }else if (obj instanceof java.sql.Date){
            java.sql.Date dt = rs.getDate(index);
            obj = new Date(dt.getTime());
        }else if (className != null &&
                ("oracle.sql.TIMESTAMP".equals(className) ||
                        "oracle.sql.TIMESTAMPTZ".equals(className))) {
            Timestamp timestamp = rs.getTimestamp(index);
            obj = new Date(timestamp.getTime());
        }else if (className != null && className.startsWith("oracle.sql.DATE")) {
            String metaDataClassName = rs.getMetaData().getColumnClassName(index);
            if ("java.sql.Timestamp".equals(metaDataClassName) ||
                    "oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
                Timestamp timestamp = rs.getTimestamp(index);
                obj = new Date(timestamp.getTime());
            }else {
                java.sql.Date dt = rs.getDate(index);
                obj = new Date(dt.getTime());
            }
        }
        return obj;
    }

    private static String getArgsInfo(Object...args){
        if(null == args || args.length==0){
            return "【No Args】";
        }
        StringBuilder s = new StringBuilder("[");
        for(Object obj : args){
            s.append(obj).append(",");
        }
        s.deleteCharAt(s.length()-1);
        s.append("]");
        return s.toString();
    }
}
